{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "Python 3.8.3 64-bit ('base': conda)",
   "display_name": "Python 3.8.3 64-bit ('base': conda)",
   "metadata": {
    "interpreter": {
     "hash": "32dff8090c6ba6f2f4bb59437073e74fc979bb703f5b904d9aee7fe2b28f04a4"
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     index    uuid  year from_paper   no  score type difficulty  \\\n",
       "0        0   20114  2020         U1   14    6.0   单选         一般   \n",
       "1        1   20115  2020         U1   15    6.0   单选         较易   \n",
       "2        2   20116  2020         U1   16    6.0   单选         一般   \n",
       "3        3   20117  2020         U1   17    6.0   单选         容易   \n",
       "4        4   20118  2020         U1   18    6.0   单选         一般   \n",
       "..     ...     ...   ...        ...  ...    ...  ...        ...   \n",
       "277    277  152332  2015         U2  332   10.0   解答         一般   \n",
       "278    278  152341  2015         U2  341    5.0   多选         一般   \n",
       "279    279  152342  2015         U2  342   10.0   解答         困难   \n",
       "280    280  152351  2015         U2  351    5.0   多选         一般   \n",
       "281    281  152352  2015         U2  352   10.0   解答         困难   \n",
       "\n",
       "     difficulty_degree  subject  \n",
       "0                  0.6       动量  \n",
       "1                  0.8  万有引力与航天  \n",
       "2                  0.5     曲线运动  \n",
       "3                  0.9      静电场  \n",
       "4                  0.5       磁场  \n",
       "..                 ...      ...  \n",
       "277                0.5       热学  \n",
       "278                0.5        光  \n",
       "279                0.1     振动和波  \n",
       "280                0.5    波粒二象性  \n",
       "281                0.1       动量  \n",
       "\n",
       "[282 rows x 10 columns]"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>index</th>\n      <th>uuid</th>\n      <th>year</th>\n      <th>from_paper</th>\n      <th>no</th>\n      <th>score</th>\n      <th>type</th>\n      <th>difficulty</th>\n      <th>difficulty_degree</th>\n      <th>subject</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>20114</td>\n      <td>2020</td>\n      <td>U1</td>\n      <td>14</td>\n      <td>6.0</td>\n      <td>单选</td>\n      <td>一般</td>\n      <td>0.6</td>\n      <td>动量</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>20115</td>\n      <td>2020</td>\n      <td>U1</td>\n      <td>15</td>\n      <td>6.0</td>\n      <td>单选</td>\n      <td>较易</td>\n      <td>0.8</td>\n      <td>万有引力与航天</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>20116</td>\n      <td>2020</td>\n      <td>U1</td>\n      <td>16</td>\n      <td>6.0</td>\n      <td>单选</td>\n      <td>一般</td>\n      <td>0.5</td>\n      <td>曲线运动</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>20117</td>\n      <td>2020</td>\n      <td>U1</td>\n      <td>17</td>\n      <td>6.0</td>\n      <td>单选</td>\n      <td>容易</td>\n      <td>0.9</td>\n      <td>静电场</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>20118</td>\n      <td>2020</td>\n      <td>U1</td>\n      <td>18</td>\n      <td>6.0</td>\n      <td>单选</td>\n      <td>一般</td>\n      <td>0.5</td>\n      <td>磁场</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>277</th>\n      <td>277</td>\n      <td>152332</td>\n      <td>2015</td>\n      <td>U2</td>\n      <td>332</td>\n      <td>10.0</td>\n      <td>解答</td>\n      <td>一般</td>\n      <td>0.5</td>\n      <td>热学</td>\n    </tr>\n    <tr>\n      <th>278</th>\n      <td>278</td>\n      <td>152341</td>\n      <td>2015</td>\n      <td>U2</td>\n      <td>341</td>\n      <td>5.0</td>\n      <td>多选</td>\n      <td>一般</td>\n      <td>0.5</td>\n      <td>光</td>\n    </tr>\n    <tr>\n      <th>279</th>\n      <td>279</td>\n      <td>152342</td>\n      <td>2015</td>\n      <td>U2</td>\n      <td>342</td>\n      <td>10.0</td>\n      <td>解答</td>\n      <td>困难</td>\n      <td>0.1</td>\n      <td>振动和波</td>\n    </tr>\n    <tr>\n      <th>280</th>\n      <td>280</td>\n      <td>152351</td>\n      <td>2015</td>\n      <td>U2</td>\n      <td>351</td>\n      <td>5.0</td>\n      <td>多选</td>\n      <td>一般</td>\n      <td>0.5</td>\n      <td>波粒二象性</td>\n    </tr>\n    <tr>\n      <th>281</th>\n      <td>281</td>\n      <td>152352</td>\n      <td>2015</td>\n      <td>U2</td>\n      <td>352</td>\n      <td>10.0</td>\n      <td>解答</td>\n      <td>困难</td>\n      <td>0.1</td>\n      <td>动量</td>\n    </tr>\n  </tbody>\n</table>\n<p>282 rows × 10 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 1
    }
   ],
   "source": [
    "from pandas.core.frame import DataFrame\n",
    "from GTexamlib.essay import Essay\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine\n",
    "import sqlite3\n",
    "from GTexamlib.imports import *\n",
    "# df=pd.read_csv(\"test.csv\")\n",
    "# engine= create_engine('sqlite:///Database.db')\n",
    "# print(df)\n",
    "# df.to_sql('question1', engine)\n",
    "\n",
    "con = sqlite3.connect(\"Database.db\")\n",
    "cur = con.cursor()\n",
    "# cur.execute(\"insert into single_sel(uuid,score) SELECT uuid,score FROM question1 where type='单选';\")\n",
    "# con.commit()\n",
    "df_question=pd.read_sql('SELECT * FROM question',con)\n",
    "df_question"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=df_question[df_question['from_paper']=='U1']\n",
    "df2=df1[df1['year']==2020]\n",
    "a=df2['uuid'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_single_sel=pd.read_sql('SELECT * FROM single_sel',con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     uuid                                               main  \\\n",
       "65  20114  行驶中的汽车如果发生剧烈碰撞,车内的安全气囊会被弹出并瞬间充满气体.若碰撞后汽车的速度在很短...   \n",
       "66  20115  火星的质量约为地球质量的$1/10,$半径约为地球半径的$1/2,$则同一物体在火星表面与在...   \n",
       "67  20116  如图,一同学表演荡秋千.已知秋千的两根绳长均为$10m,$该同学和秋千踏板的总质量约为$50...   \n",
       "68  20117  图(a)所示的电路中,K与L间接一智能电源,用以控制电容器C两端的电压$U_C.$如果$U_...   \n",
       "69  20118  一匀强磁场的磁感应强度大小为B,方向垂直于纸面向外,其边界如图中虚线所示$,ab$为半圆$,...   \n",
       "\n",
       "                                               figure              choice_a  \\\n",
       "65                                               None        增加了司机单位面积的受力大小   \n",
       "66                                               None                 $0.2$   \n",
       "67  iVBORw0KGgoAAAANSUhEUgAAAQcAAAFSCAIAAABwio20AA...                $200N$   \n",
       "68  b'iVBORw0KGgoAAAANSUhEUgAAAtwAAAEQCAYAAACDa6M7...                  None   \n",
       "69  b'iVBORw0KGgoAAAANSUhEUgAAAVQAAADgCAYAAABcvp5i...  $\\frac{7\\pi m}{6qB}$   \n",
       "\n",
       "                choice_b              choice_c               choice_d answer  \\\n",
       "65       减少了碰撞前后司机动量的变化量      将司机的动能全部转换成汽车的动能  延长了司机的受力时间并增大了司机的受力面积      D   \n",
       "66                 $0.4$                 $2.0$                  $2.5$      B   \n",
       "67                $400N$                $600N$                 $800N$      B   \n",
       "68                  None                  None                   None      A   \n",
       "69  $\\frac{5\\pi m}{4qB}$  $\\frac{4\\pi m}{3qB}$   $\\frac{3\\pi m}{2qB}$      C   \n",
       "\n",
       "   solution  score  \n",
       "65     None      6  \n",
       "66     None      6  \n",
       "67     None      6  \n",
       "68     None      6  \n",
       "69     None      6  "
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>uuid</th>\n      <th>main</th>\n      <th>figure</th>\n      <th>choice_a</th>\n      <th>choice_b</th>\n      <th>choice_c</th>\n      <th>choice_d</th>\n      <th>answer</th>\n      <th>solution</th>\n      <th>score</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>65</th>\n      <td>20114</td>\n      <td>行驶中的汽车如果发生剧烈碰撞,车内的安全气囊会被弹出并瞬间充满气体.若碰撞后汽车的速度在很短...</td>\n      <td>None</td>\n      <td>增加了司机单位面积的受力大小</td>\n      <td>减少了碰撞前后司机动量的变化量</td>\n      <td>将司机的动能全部转换成汽车的动能</td>\n      <td>延长了司机的受力时间并增大了司机的受力面积</td>\n      <td>D</td>\n      <td>None</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>66</th>\n      <td>20115</td>\n      <td>火星的质量约为地球质量的$1/10,$半径约为地球半径的$1/2,$则同一物体在火星表面与在...</td>\n      <td>None</td>\n      <td>$0.2$</td>\n      <td>$0.4$</td>\n      <td>$2.0$</td>\n      <td>$2.5$</td>\n      <td>B</td>\n      <td>None</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>67</th>\n      <td>20116</td>\n      <td>如图,一同学表演荡秋千.已知秋千的两根绳长均为$10m,$该同学和秋千踏板的总质量约为$50...</td>\n      <td>iVBORw0KGgoAAAANSUhEUgAAAQcAAAFSCAIAAABwio20AA...</td>\n      <td>$200N$</td>\n      <td>$400N$</td>\n      <td>$600N$</td>\n      <td>$800N$</td>\n      <td>B</td>\n      <td>None</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>68</th>\n      <td>20117</td>\n      <td>图(a)所示的电路中,K与L间接一智能电源,用以控制电容器C两端的电压$U_C.$如果$U_...</td>\n      <td>b'iVBORw0KGgoAAAANSUhEUgAAAtwAAAEQCAYAAACDa6M7...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>A</td>\n      <td>None</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>69</th>\n      <td>20118</td>\n      <td>一匀强磁场的磁感应强度大小为B,方向垂直于纸面向外,其边界如图中虚线所示$,ab$为半圆$,...</td>\n      <td>b'iVBORw0KGgoAAAANSUhEUgAAAVQAAADgCAYAAABcvp5i...</td>\n      <td>$\\frac{7\\pi m}{6qB}$</td>\n      <td>$\\frac{5\\pi m}{4qB}$</td>\n      <td>$\\frac{4\\pi m}{3qB}$</td>\n      <td>$\\frac{3\\pi m}{2qB}$</td>\n      <td>C</td>\n      <td>None</td>\n      <td>6</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "df_single_sel[df_single_sel['uuid'].isin(a)]"
   ]
  }
 ]
}